#region Copyright (c) 2006-2010 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2010 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Linq;
using System.Xml;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.Common.Util;
using System.Collections.Generic;
using Widgetsphere.Generator.Forms;
using System.Windows.Forms;
using Widgetsphere.Generator.Common.Logging;
using System.Threading;
using Widgetsphere.Generator.Common.Forms;

namespace Widgetsphere.Generator
{
  internal class SqlSchemaToModel
	{
		#region Class Members

		private string _connectString;
    private string _dbName;

		#endregion

		#region Constructors

		public SqlSchemaToModel(string connectString)
    {
      this._connectString = connectString;
      this._dbName = new System.Data.SqlClient.SqlConnection(this._connectString).Database;
		}

		#endregion

		#region Events

		public event EventHandler<ProgressEventArgs> Progress;

		protected virtual void OnProgress(ProgressEventArgs e)
		{
			if (this.Progress != null)
				this.Progress(this, e);
		}

		#endregion

		/// <summary>
		/// Load a database schema
		/// </summary>
		/// <param name="project">The project object to load from database</param>
		public void GetProjectFromSqlSchema(WidgetsphereGeneratorProject project)
		{
			GetProjectFromSqlSchema(project, true);
		}

		/// <summary>
		/// Load a database schema
		/// </summary>
		/// <param name="project">The project object to load from database</param>
		/// <param name="refreshModel">Determines whether to load all model controllers and other UI components</param>
		public void GetProjectFromSqlSchema(WidgetsphereGeneratorProject project, bool refreshModel)
    {
			try
			{
				ModelRoot root = (ModelRoot)project.RootController.Object;
				string companyName = GetCompanyName();
				string projectName = GetProjectName();
				string databaseName = GetDatabaseName();
				if ((companyName != null) && (companyName != ""))
					root.CompanyName = companyName;
				if ((projectName != null) && (projectName != ""))
					root.ProjectName = projectName;
				if (databaseName != "")
					root.Database.DatabaseName = databaseName;
				root.Version = "0.0.0.0";
				root.SQLServerType = GetSQLVersion();

				root.Database.Columns.Clear();
				root.Database.Relations.Clear();
				root.Database.Tables.Clear();

				string databaseCollation = GetDatabaseCollation();

				//Progress
				this.OnProgress(new ProgressEventArgs() { PercentDone = 0, Text = "Loading..." });

				int tableCount = GetTableCount(_connectString);
				int tableIndex = 1;

				#region Tables

				SqlDataReader tableReader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, GetSqlDatabaseTables());
				while (tableReader.Read())
				{
					DateTime startTime1 = DateTime.Now;

					Table currentTable = root.Database.Tables.Add(tableReader["name"].ToString());
					currentTable.DBSchema = tableReader["schema"].ToString();

					//Progress
					this.OnProgress(new ProgressEventArgs()
					{
						PercentDone = (int)(tableIndex * 100.0 / tableCount),
						Text = currentTable.Name
					});

					if (!ValidationHelper.ValidDatabaseIdenitifer(currentTable.Name))
					{
						currentTable.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentTable.Name);
					}

					//Table Attributes
					SqlDataReader tableAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, "SELECT name, value FROM  ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + currentTable.Name + "', default, default)");
					while (tableAttributeReader.Read())
					{
						string attribName = string.Empty;
						attribName = tableAttributeReader["name"].ToString();
						if (attribName.ToLower().Equals("ms_description"))
						{
							currentTable.Description = tableAttributeReader["value"].ToString();
						}
						else if (attribName.ToLower().Equals("generated"))
						{
							currentTable.Generated = bool.Parse(tableAttributeReader["value"].ToString());
						}
						else if (attribName.ToLower().Equals("associative"))
						{
							currentTable.AssociativeTable = bool.Parse(tableAttributeReader["value"].ToString());
						}
						else if (attribName.ToLower().Equals("hasHistory"))
						{
							currentTable.HasHistory = bool.Parse(tableAttributeReader["value"].ToString());
						}
						System.Windows.Forms.Application.DoEvents();
					}
					tableAttributeReader.Close();

					#region Columns
					DateTime startTime2 = DateTime.Now;
					IDbConnection connection = DatabaseHelper.GetConnection(_connectString);
					SqlDataReader columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, GetSqlColumnsForTable(tableReader["name"].ToString()));
					while (columnReader.Read())
					{
						string columnName = columnReader["name"].ToString();
						if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedByColumnName) || StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedDateColumnName))
						{
							currentTable.AllowCreateAudit = true;
						}
						else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedByColumnName) || StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedDateColumnName) ||
							StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date"))
						{
							currentTable.AllowModifiedAudit = true;
						}
						else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.TimestampColumnName))
						{
							currentTable.AllowTimestamp = true;
						}
						else
						{
							Column currentColumn = root.Database.Columns.Add(columnName);

							if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name))
							{
								currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentColumn.Name);
							}

							currentColumn.ParentTableRef = currentTable.CreateRef();
							currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString());
							if (bool.Parse(columnReader["isIdentity"].ToString()))
								currentColumn.Identity = IdentityTypeConstants.Database;
							else
								currentColumn.Identity = IdentityTypeConstants.None;
							string defaultvalue = columnReader["defaultValue"].ToString().Trim(new char[] { '(', '\'', ')' });
							defaultvalue = defaultvalue.Replace("\n", "");
							defaultvalue = defaultvalue.Replace("\r", "");

							//This is some sort of default pointer, we do not handle this.
							if (defaultvalue.StartsWith("create default ["))
								defaultvalue = "";

							//Just in case some put 'null' in to the default field
							if (currentColumn.AllowNull && defaultvalue.ToLower() == "null")
								defaultvalue = "";

							currentColumn.Default = defaultvalue;

							currentColumn.Length = (int)columnReader["length"];
							if (columnReader["isPrimaryKey"] != System.DBNull.Value)
								currentColumn.PrimaryKey = true;

							//currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString());
							try
							{
								string columnTypename = columnReader["datatype"].ToString();
								if (StringHelper.Match(columnTypename, "numeric", true))
									currentColumn.DataType = SqlDbType.Decimal;
								else
									currentColumn.DataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true);
							}
							catch { }

							//Decimals are a little different
							if (currentColumn.DataType == SqlDbType.Decimal)
							{
								currentColumn.Length = (byte)columnReader["precision"];
								currentColumn.Scale = (int)columnReader["scale"];
							}

							if (columnReader["collation"] != System.DBNull.Value)
							{
								if (databaseCollation != (string)columnReader["collation"])
									currentColumn.Collate = (string)columnReader["collation"];
							}

							DateTime startTime3 = DateTime.Now;
							SqlDataReader columnAttributeReader = DatabaseHelper.ExecuteReader(connection, CommandType.Text, "SELECT name, value FROM  ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + currentTable + "', 'column', '" + currentColumn + "')");
							while (columnAttributeReader.Read())
							{
								string attribName = string.Empty;
								attribName = columnAttributeReader["name"].ToString();
								if (attribName.ToLower().Equals("ms_description"))
								{
									currentColumn.Description = columnAttributeReader["value"].ToString();
								}
							}
							columnAttributeReader.Close();
							DateTime endTime3 = DateTime.Now;
							//System.Diagnostics.Debug.WriteLine("Time 3: " + endTime3.Subtract(startTime3).TotalMilliseconds.ToString("###,###,###"));

							currentTable.Columns.Add(currentColumn.CreateRef());
						} //Create New Column
					}
					columnReader.Close();
					DateTime endTime2 = DateTime.Now;
					//System.Diagnostics.Debug.WriteLine("Table '" + currentTable.Name + "' Load Columns: " + endTime2.Subtract(startTime2).TotalMilliseconds.ToString("###,###,###"));
					#endregion

					#region Columns Extra Info

					columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, GetSqlColumnInfoAuxForTable(tableReader["name"].ToString()));
					while (columnReader.Read())
					{
						string columnName = columnReader["name"].ToString();
						Column currentColumn = root.Database.Columns[columnName];
						if (currentColumn != null)
						{
							currentColumn.ComputedColumn = true;
							currentColumn.Formula = columnReader["formula"].ToString();
						}
					}
					columnReader.Close();

					#endregion

					connection.Close();
					System.Windows.Forms.Application.DoEvents();

					DateTime endTime1 = DateTime.Now;
					//System.Diagnostics.Debug.WriteLine("Table '" + currentTable.Name + "': " + endTime1.Subtract(startTime1).TotalMilliseconds.ToString("###,###,###"));

					if (currentTable.Name == "sysdiagrams")
					{
						root.Database.Tables.Remove(currentTable);
					}
					tableIndex++;

				}
				tableReader.Close();

				#endregion

				#region Relations

				DateTime startTimeRelation = DateTime.Now;
				this.LoadRelations(project);
				DateTime endTimeRelation = DateTime.Now;
				//System.Diagnostics.Debug.WriteLine("Load Relations: " + endTimeRelation.Subtract(startTimeRelation).TotalMilliseconds.ToString("###,###,###"));

				#endregion

				#region Views

				DateTime startTimeV = DateTime.Now;
				this.LoadViews(project);
				DateTime endTimeV = DateTime.Now;
				//System.Diagnostics.Debug.WriteLine("Load Views: " + endTimeV.Subtract(startTimeV).TotalMilliseconds.ToString("###,###,###"));

				#endregion

				#region Stored Procedures

				DateTime startTimeSP = DateTime.Now;
				this.LoadStoredProcedures(project);
				DateTime endTimeSP = DateTime.Now;
				//System.Diagnostics.Debug.WriteLine("Load Stored Procedures: " + endTimeSP.Subtract(startTimeSP).TotalMilliseconds.ToString("###,###,###"));				

				#endregion

				//Refresh the tree
				if (refreshModel)
				{
					//project.RootController.Node.RefreshDeep();
					//Thread t = new Thread(new ThreadStart(project.RootController.Node.RefreshDeep));
					//t.Start();
				}

				//Progress
				this.OnProgress(new ProgressEventArgs() { PercentDone = 100, Text = "Complete" });

			}
			catch (Exception ex)
			{				
				throw;
			}
			finally
			{
				//Progress
				this.OnProgress(new ProgressEventArgs() { PercentDone = 100, Text = "Complete" });
			}
		}

		public static IEnumerable<string> GetTableListFromDatabase(string connectionString)
		{
			List<string> retval = new List<string>();
			SqlDataReader tableReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlDatabaseTables());
			while (tableReader.Read())
			{
				retval.Add(tableReader["name"].ToString());
			}
			tableReader.Close();
			return retval;
		}

		public static IEnumerable<Column> GetTableDefinitionFromDatabase(string connectionString, string tableName, ModelRoot root)
		{
			try
			{
				List<Column> retval = new List<Column>();

				//Columns
				IDbConnection connection = DatabaseHelper.GetConnection(connectionString);
				SqlDataReader columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlColumnsForTable(tableName));
				while (columnReader.Read())
				{
					string columnName = columnReader["name"].ToString();
					if (StringHelper.Match(columnName, root.Database.CreatedByColumnName) || StringHelper.Match(columnName, root.Database.CreatedDateColumnName))
					{
						//Do Nothing
					}
					else if (StringHelper.Match(columnName, root.Database.ModifiedByColumnName) || StringHelper.Match(columnName, root.Database.ModifiedDateColumnName) ||
						StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date"))
					{
						//Do Nothing
					}
					else if (StringHelper.Match(columnName, root.Database.TimestampColumnName))
					{
						//Do Nothing
					}
					else
					{
						Column currentColumn = new Column(root);
						currentColumn.Name = columnName;

						if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name))
						{
							currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentColumn.Name);
						}

						//currentColumn.ParentTableRef = currentTable.CreateRef();
						currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString());
						if (bool.Parse(columnReader["isIdentity"].ToString()))
							currentColumn.Identity = IdentityTypeConstants.Database;
						else
							currentColumn.Identity = IdentityTypeConstants.None;
						string defaultvalue = columnReader["defaultValue"].ToString().Trim(new char[] { '(', '\'', ')' });
						defaultvalue = defaultvalue.Replace("\n", "");
						defaultvalue = defaultvalue.Replace("\r", "");

						//This is some sort of default pointer, we do not handle this.
						if (defaultvalue.StartsWith("create default ["))
							defaultvalue = "";

						//Just in case some put 'null' in to the default field
						if (currentColumn.AllowNull && defaultvalue.ToLower() == "null")
							defaultvalue = "";

						currentColumn.Default = defaultvalue;

						currentColumn.Length = (int)columnReader["length"];
						if (columnReader["isPrimaryKey"] != System.DBNull.Value)
							currentColumn.PrimaryKey = true;

						//currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString());
						try
						{
							string columnTypename = columnReader["datatype"].ToString();
							if (StringHelper.Match(columnTypename, "numeric", true))
								currentColumn.DataType = SqlDbType.Decimal;
							else
								currentColumn.DataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true);
						}
						catch { }
						SqlDataReader columnAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connection, CommandType.Text, "SELECT name, value FROM  ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + tableName + "', 'column', '" + currentColumn + "')");
						while (columnAttributeReader.Read())
						{
							string attribName = string.Empty;
							attribName = columnAttributeReader["name"].ToString();
							if (attribName.ToLower().Equals("ms_description"))
							{
								currentColumn.Description = columnAttributeReader["value"].ToString();
							}

						}
						columnAttributeReader.Close();
						retval.Add(currentColumn);
					} //Create New Column
				}
				columnReader.Close();
				return retval;

			}
			catch (Exception ex)
			{				
				throw;
			}
		}

		#region Private Methods

		private int GetTableCount(string connectString)
		{
			DataSet ds = DatabaseHelper.ExecuteDataset(connectString, "select count(*) from sysobjects where Type = 'U'");
			return (int)ds.Tables[0].Rows[0][0];
		}

		private void LoadViews(WidgetsphereGeneratorProject project)
		{
			ModelRoot root = (ModelRoot)project.RootController.Object;
			DataSet dsView = DatabaseHelper.ExecuteDataset(_connectString, GetSqlForViews());
			DataSet dsViewColumn = DatabaseHelper.ExecuteDataset(_connectString, GetSqlForViewsColumns());

			//Add the Views
			if (dsView.Tables.Count > 0)
			{
				foreach (DataRow rowView in dsView.Tables[0].Rows)
				{
					int id = (int)rowView["id"];
					string name = (string)rowView["name"];
					string sql = (string)rowView["sql"];
					CustomView customView = root.Database.CustomViews.FirstOrDefault(x => x.Name == name);
					if (customView == null)
					{
						customView = ((ModelRoot)project.RootController.Object).Database.CustomViews.Add();
						customView.Name = name;
						customView.SQL = sql;
					}
				}
			}

			//Add the columns
			if (dsViewColumn.Tables.Count > 0)
			{
				foreach (DataRow rowView in dsViewColumn.Tables[0].Rows)
				{
					int id = (int)rowView["id"];
					string spName = (string)rowView["name"];
					string name = (string)rowView["ColName"];
					string typeName = (string)rowView["ColType"];
					int length = int.Parse(rowView["length"].ToString());
					CustomView customView = root.Database.CustomViews.FirstOrDefault(x => x.Name == spName);
					if (customView != null)
					{
						CustomViewColumn column = root.Database.CustomViewColumns.Add();
						column.Name = name;
						column.Length = length;
						column.DataType = DatabaseHelper.GetSQLDataType(typeName);
						customView.Columns.Add(column.CreateRef());
						column.ParentViewRef = customView.CreateRef();
					}
				}
			}

		}

		private void LoadStoredProcedures(WidgetsphereGeneratorProject project)
		{
			try
			{
				ModelRoot root = (ModelRoot)project.RootController.Object;
				DataSet dsSP = DatabaseHelper.ExecuteDataset(_connectString, GetSqlForStoredProcedures());
				DataSet dsSPColumn = DatabaseHelper.ExecuteDataset(_connectString, GetSqlForStoredProceduresParameters());

				//Add the Stored Procedures
				foreach (DataRow rowSP in dsSP.Tables[0].Rows)
				{
					int id = (int)rowSP["id"];
					string name = (string)rowSP["name"];					
					CustomStoredProcedure customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == name);
					if (customStoredProcedure == null)
					{
						customStoredProcedure = ((ModelRoot)project.RootController.Object).Database.CustomStoredProcedures.Add();
						customStoredProcedure.Name = name;
						customStoredProcedure.SQL = GetSqlForStoredProceduresBody(name);
					}

				}

				////Add the columns
				//foreach (DataRow rowSP in dsSPColumn.Tables[0].Rows)
				//{
				//  int id = (int)rowSP["id"];
				//  string spName = (string)rowSP["name"];
				//  string name = (string)rowSP["ColName"];
				//  string typeName = (string)rowSP["ColType"];
				//  CustomStoredProcedure customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == spName);
				//  if (customStoredProcedure != null)
				//  {
				//    CustomStoredProcedureColumn column = root.Database.CustomStoredProcedureColumns.Add();
				//    column.Name = name;
				//    column.DataType = DatabaseHelper.GetSQLDataType(typeName);
				//    customStoredProcedure.Columns.Add(column.CreateRef());
				//    column.ParentRef = customStoredProcedure.CreateRef();
				//  }
				//}

				//Add the parameters
				foreach (DataRow rowSP in dsSPColumn.Tables[0].Rows)
				{
					int id = (int)rowSP["id"];
					string spName = (string)rowSP["name"];
					string name = (string)rowSP["ColName"];
					string typeName = (string)rowSP["ColType"];
					int length = int.Parse(rowSP["length"].ToString());
					CustomStoredProcedure customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == spName);
					if (customStoredProcedure != null)
					{
						Parameter parameter = root.Database.CustomRetrieveRuleParameters.Add();
						parameter.Name = name.Replace("@", "");
						parameter.DataType = DatabaseHelper.GetSQLDataType(typeName);
						parameter.Length = length;
						customStoredProcedure.Parameters.Add(parameter.CreateRef());
						parameter.ParentTableRef = customStoredProcedure.CreateRef();
					}
				}

			}
			catch (Exception ex)
			{
				throw;
			}
		}

		private void LoadRelations(WidgetsphereGeneratorProject project)
		{
			ModelRoot root = (ModelRoot)project.RootController.Object;
			DataSet dsRelationship = DatabaseHelper.ExecuteDataset(_connectString, GetSqlForRelationships(""));
			foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
			{
				string constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
				string parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
				string childTableName = (string)rowRelationship["FK_TABLE_NAME"];
				Table parentTable = root.Database.Tables[parentTableName];
				Table childTable = root.Database.Tables[childTableName];

				if ((parentTable != null) && (childTable != null))
				{
					Relation relation = null;
					if (!root.Database.Relations.Contains(constraintName))
					{
						relation = root.Database.Relations.Add();
						relation.ParentTableRef = parentTable.CreateRef();
						relation.ChildTableRef = childTable.CreateRef();
						relation.ConstraintName = constraintName;
						parentTable.Relationships.Add(relation.CreateRef());
					}
					else
					{
						relation = root.Database.Relations.GetByName(constraintName);
					}

					//add the column relationship to the relation
					ColumnRelationship columnRelationship = new ColumnRelationship(relation.Root);
					columnRelationship.ParentColumnRef = ((Column)parentTable.Columns[(string)rowRelationship["UQ_COLUMN_NAME"]].Object).CreateRef();
					columnRelationship.ChildColumnRef = ((Column)childTable.Columns[(string)rowRelationship["FK_COLUMN_NAME"]].Object).CreateRef();
					relation.ColumnRelationships.Add(columnRelationship);
				} //Not Contains constraint

			}
		}

		private SQLServerTypeConstants GetSQLVersion()
		{
			DataSet ds = DatabaseHelper.ExecuteDataset(_connectString, "SELECT SERVERPROPERTY('productversion')");
			string version = (string)ds.Tables[0].Rows[0][0];
			if (version.StartsWith("10."))
				return SQLServerTypeConstants.SQL2008;
			else
				return SQLServerTypeConstants.SQL2005;
		}

    private string GetProjectName()
    {
      string retval = null;
      SqlDataReader reader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'projectName',   NULL, NULL,NULL, NULL,NULL,NULL)");
			if (reader.Read()) retval = reader["value"].ToString();
			reader.Close();
      return retval;
    }

    private string GetCompanyName()
    {
      string retval = null;
			try
			{
				SqlDataReader reader = (SqlDataReader)DatabaseHelper.ExecuteReader(_connectString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'companyName',NULL, NULL,NULL, NULL,NULL,NULL)");
				if (reader.Read()) retval = reader["value"].ToString();
				reader.Close();
			}
			catch (Exception ex)
			{
				WSLog.LogError(ex);
				throw ex;
			}
      return retval;
    }

    private string GetDatabaseName()
    {
      return _dbName;
    }

    private string GetSqlDatabaseProperties()
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT	sysproperties.name,	sysproperties.value ");
      sb.Append("FROM	sysproperties ");
      sb.Append("WHERE	sysproperties.id=0");
      return sb.ToString();
    }

    private static string GetSqlDatabaseTables()
    {
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("DECLARE @bar varchar(150)");
			sb.AppendLine("DECLARE @val varchar(150)");
			sb.AppendLine("DECLARE @tab table");
			sb.AppendLine("(");
			sb.AppendLine("xName varchar(150) NOT NULL,");
			sb.AppendLine("xValue varchar(150) NULL,");
			sb.AppendLine("xSchema varchar(150) NOT NULL");
			sb.AppendLine(")");
			sb.AppendLine("INSERT INTO @tab SELECT so.name, null, sc.name [schema] FROM sys.tables so INNER JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name <> 'dtproperties' AND so.name <> 'sysdiagrams'");
			sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab ORDER BY xName ASC)");
			sb.AppendLine("WHILE @bar is not null");
			sb.AppendLine("BEGIN");
			sb.AppendLine("SET @val = (SELECT CONVERT(varchar(150), value) FROM ::fn_listextendedproperty ( N'selectionCriteria', N'user', N'dbo', N'table',@bar,NULL,NULL))");
			sb.AppendLine("UPDATE @tab	SET xValue = @val where xName = @bar");
			sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab where xName > @bar ORDER BY xName ASC)");
			sb.AppendLine("END");
			sb.AppendLine("select xName as name, xSchema as [schema], xValue selectionCriteria from @tab ORDER BY xName");
			return sb.ToString();
    }

    private string GetSqlPropertiesForTable(string tableName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT sysproperties.name, sysproperties.value ");
      sb.Append("FROM sysproperties ");
      sb.Append("INNER JOIN sysobjects on sysproperties.id = sysobjects.id ");
      sb.Append("WHERE sysobjects.name='").Append(tableName).Append("' AND ");
      sb.Append("sysproperties.smallid=0");
      return sb.ToString();
    }

		private string GetSqlColumsDescription(string tableName)
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,");
			sb.AppendLine("       ep.value AS Description");
			sb.AppendLine("FROM sys.objects");
			sb.AppendLine("INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id");
			sb.AppendLine("CROSS APPLY fn_listextendedproperty(default,");
			sb.AppendLine("                  'SCHEMA', schema_name(schema_id),");
			sb.AppendLine("                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep");
			sb.AppendLine("WHERE sys.objects.name = '" + tableName + "'");
			return sb.ToString();
		}

		private string GetDatabaseCollation()
		{
			System.Data.SqlClient.SqlConnection connection = null;
			try
			{
				string sql = "SELECT DATABASEPROPERTYEX('" + _dbName + "', 'Collation') SQLCOLLATION";
				connection = new System.Data.SqlClient.SqlConnection(_connectString);
				SqlCommand command = new SqlCommand(sql, connection);
				connection.Open();
				string retval = (string)command.ExecuteScalar();
				return retval;
			}
			catch (Exception ex)
			{
				throw;
			}
			finally
			{
				if (connection != null && connection.State == ConnectionState.Open)
					connection.Close();
			}
		}

		private static string GetSqlColumnInfoAuxForTable(string tableName)
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT [name], [text] as formula");
			sb.AppendLine("FROM syscolumns cl JOIN syscomments cm");
			sb.AppendLine("ON cl.id = cm.id");
			sb.AppendLine("AND cm.number = cl.colid");
			sb.AppendLine("WHERE iscomputed = 1 AND object_name(cl.id) = '" + tableName + "'");
			return sb.ToString();
		}

    private static string GetSqlColumnsForTable(string tableName)
    {
			StringBuilder sb = new StringBuilder();
      sb.AppendLine(" SELECT ");
      sb.AppendLine(" 	c.ORDINAL_POSITION as colorder,");
      sb.AppendLine(" 	c.COLUMN_NAME as name,");
			sb.AppendLine("(");
			sb.AppendLine("select top 1 c1.name");
			sb.AppendLine("from sysindexes i");
			sb.AppendLine("join sysobjects o ON i.id = o.id");
			sb.AppendLine("join sysobjects pk ON i.name = pk.name");
			sb.AppendLine("AND pk.parent_obj = i.id");
			sb.AppendLine("AND pk.xtype = 'PK'");
			sb.AppendLine("join sysindexkeys ik on i.id = ik.id");
			sb.AppendLine("and i.indid = ik.indid");
			sb.AppendLine("join syscolumns c1 ON ik.id = c1.id");
			sb.AppendLine("AND ik.colid = c1.colid");
			sb.AppendLine("AND c1.name = c.COLUMN_NAME");
			sb.AppendLine("where o.name = '" + tableName + "'");
			sb.AppendLine(") as [isPrimaryKey],");
      sb.AppendLine(" 	case WHEN");
      sb.AppendLine(" 	(");
      sb.AppendLine(" 		SELECT ");
      sb.AppendLine(" 				count(*) ");
      sb.AppendLine(" 			FROM ");
      sb.AppendLine(" 				INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE foreignkeyccu");
      sb.AppendLine(" 				INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS foreignkeytc on foreignkeyccu.CONSTRAINT_NAME = foreignkeytc.CONSTRAINT_NAME AND");
      sb.AppendLine(" 																												foreignkeyccu.CONSTRAINT_SCHEMA = foreignkeytc.CONSTRAINT_SCHEMA AND");
      sb.AppendLine(" 																												foreignkeytc.CONSTRAINT_TYPE = 'FOREIGN KEY'");
      sb.AppendLine(" 			WHERE");
      sb.AppendLine(" 				foreignkeyccu.TABLE_SCHEMA = c.TABLE_SCHEMA AND");
      sb.AppendLine(" 				foreignkeyccu.TABLE_NAME = c.TABLE_NAME AND");
      sb.AppendLine(" 				foreignkeyccu.COLUMN_NAME = c.COLUMN_NAME ");
      sb.AppendLine(" 	) > 0 THEN 'true' ELSE 'false' END as isForeignKey,");
      sb.AppendLine(" 	c.DATA_TYPE as datatype,");
			sb.AppendLine(" 	c.numeric_precision AS [precision], c.numeric_scale AS [scale],");
      sb.AppendLine(" 		case when	c.CHARACTER_MAXIMUM_LENGTH is null or c.CHARACTER_MAXIMUM_LENGTH > 8000 then s.length else c.CHARACTER_MAXIMUM_LENGTH end as length,");
      sb.AppendLine(" 	case when c.IS_NULLABLE = 'No' then 'false' else 'true' end as allowNull,  ");
			sb.AppendLine(" 	case when c.COLUMN_DEFAULT is null then '' else REPLACE(REPLACE(REPLACE(REPLACE(c.COLUMN_DEFAULT,'(N''',''),')',''),'(',''),'''','') end as defaultValue,");
			sb.AppendLine(" 	case when COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'true' else 'false' end as isIdentity,");
			sb.AppendLine(" 	collation");
      sb.AppendLine(" FROM ");
      sb.AppendLine(" 	INFORMATION_SCHEMA.COLUMNS c ");
      sb.AppendLine(" 	INNER JOIN systypes s on s.name = c.DATA_TYPE");
      sb.AppendLine(" WHERE");
			sb.AppendLine(" 	c.TABLE_NAME  = '" + tableName + "'");
      sb.AppendLine(" ORDER BY");
      sb.AppendLine(" 	c.TABLE_NAME,");
      sb.AppendLine(" 	c.COLUMN_NAME");
      return sb.ToString();
    }
		
    private string GetSqlForColumnProperties(string tableName, string columnName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("SELECT sysproperties.name,	sysproperties.value");
      sb.Append("FROM sysproperties");
      sb.Append("inner join sysobjects on sysproperties.id = sysobjects.id");
      sb.Append("inner join syscolumns on syscolumns.id = sysobjects.id AND");
      sb.Append("syscolumns.colorder = sysproperties.smallid");
      sb.Append("WHERE");
      sb.Append("sysobjects.name = '").Append(tableName).Append("' AND ");
      sb.Append("syscolumns.name = '").Append(columnName).Append("'");
      return sb.ToString();
    }

    private string GetSqlForRelationships(string tableName)
    {
			//StringBuilder sb = new StringBuilder();
			//sb.Append("DECLARE @table_name varchar(256) ");
			//sb.Append("set @table_name = '").Append(tableName).Append("' ");
			//sb.Append("SELECT DISTINCT  ");
			//sb.Append("	parent.name as Parent,  ");
			//sb.Append("	child.name as Child,  ");
			//sb.Append("	case when parent.name = @table_name then 'parent' else 'child' end as rolePlayed,  ");
			//sb.Append("	relation.name as constraintName,  ");
			//sb.Append("	case when roleNameProvider.value is null then '' else roleNameProvider.value end as roleName  ");
			//sb.Append("FROM  ");
			//sb.Append("	sysforeignkeys  ");
			//sb.Append("		inner join sysobjects relation on constid = relation.id ");
			//sb.Append("		inner join sysobjects child on fkeyid = child.id  ");
			//sb.Append("		inner join sysobjects parent on rkeyid = parent.id   ");
			//sb.Append("		left outer join sysproperties roleNameProvider on roleNameProvider.id = relation.id  ");
			//sb.Append("WHERE  ");
			//sb.Append("	parent.name = @table_name  ");
			//sb.Append("OR  ");
			//sb.Append("	child.name=@table_name ");
			//sb.Append("union ");
			//sb.Append("SELECT  UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 )) as Parent, ");
			//sb.Append("c.TABLE_NAME as Child, ");
			//sb.Append("'child', ");
			//sb.Append("'PSEUDO_' + c.TABLE_NAME + '_' + UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 ) + '_' + c.COLUMN_NAME), ");
			//sb.Append("c.COLUMN_NAME ");
			//sb.Append("FROM ");
			//sb.Append("	INFORMATION_SCHEMA.COLUMNS c ");
			//sb.Append("WHERE  ");
			//sb.Append("	c.TABLE_NAME = @table_name ");
			//sb.Append("and  ");
			//sb.Append("	c.COLUMN_NAME like 'fk_to_%' ");
			//sb.Append("union ");
			//sb.Append("SELECT DISTINCT ");
			//sb.Append("	@table_name as Parent, ");
			//sb.Append("c.TABLE_NAME as Child,  ");
			//sb.Append("'parent' as rolePlayed, ");
			//sb.Append("'PSEUDO_' + c.TABLE_NAME + '_' + UPPER(SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 ) + '_' + c.COLUMN_NAME) as constraintName, ");
			//sb.Append("c.COLUMN_NAME as roleName ");
			//sb.Append("FROM  ");
			//sb.Append("	INFORMATION_SCHEMA.COLUMNS c ");
			//sb.Append("WHERE ");
			//sb.Append("c.COLUMN_NAME like 'fk_to_%' ");
			//sb.Append("AND ");
			//sb.Append("@table_name = SUBSTRING( c.COLUMN_NAME ,7, CHARINDEX ( '__' ,c.COLUMN_NAME) - 7 )");
			//return sb.ToString();

			//StringBuilder sb = new StringBuilder();
			//sb.Append("SELECT ");
			//sb.Append("FK.TABLE_NAME AS [Child], ");
			//sb.Append("CU.COLUMN_NAME AS [ChildColumn], ");
			//sb.Append("PK.TABLE_NAME AS [Parent], ");
			//sb.Append("PT.COLUMN_NAME AS [ParentColumn], ");
			//sb.Append("C.CONSTRAINT_NAME AS [constraintName] ");
			//sb.Append("FROM    	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ");
			//sb.Append("INNER JOIN 	INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN    	INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN    	INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME ");
			//sb.Append("INNER JOIN	( ");
			//sb.Append("SELECT 		i1.TABLE_NAME, i2.COLUMN_NAME ");
			//sb.Append("FROM   		INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 ");
			//sb.Append("INNER JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME ");
			//sb.Append("WHERE 		i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ");
			//sb.Append(") PT ON PT.TABLE_NAME = PK.TABLE_NAME ");

			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT ");
			sb.AppendLine("		 KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'");
			sb.AppendLine("	, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'");
			sb.AppendLine("	, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'   ");
			sb.AppendLine("	, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'");
			sb.AppendLine("	, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'   ");
			sb.AppendLine("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC");
			sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1");
			sb.AppendLine("ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG ");
			sb.AppendLine("	AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA");
			sb.AppendLine("	AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
			sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2");
			sb.AppendLine("ON KCU2.CONSTRAINT_CATALOG = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_CATALOG ");
			sb.AppendLine("	AND KCU2.CONSTRAINT_SCHEMA = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_SCHEMA");
			sb.AppendLine("	AND KCU2.CONSTRAINT_NAME = ");
			sb.AppendLine("RC.UNIQUE_CONSTRAINT_NAME");
			sb.AppendLine("	AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("	KCU1.CONSTRAINT_NAME,");
			sb.AppendLine("	KCU1.ORDINAL_POSITION");
			return sb.ToString();		
		}

		private string GetSqlForViewsColumns()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT	dbo.syscolumns.length, dbo.sysobjects.name, dbo.sysobjects.id,");
			sb.AppendLine("		dbo.syscolumns.name AS ColName,");
			sb.AppendLine("		dbo.systypes.name AS ColType");
			sb.AppendLine("FROM	dbo.sysobjects INNER JOIN");
			sb.AppendLine("		dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN");
			sb.AppendLine("		dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype");
			sb.AppendLine("WHERE	(dbo.sysobjects.category = 0) AND");
			sb.AppendLine("		(dbo.sysobjects.xtype = 'V') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'gen_%') AND");
			sb.AppendLine("		dbo.systypes.name <> 'sysname'");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("		dbo.sysobjects.name, dbo.syscolumns.name");
			return sb.ToString();
		}

		private string GetSqlForViews()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT	dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.sysobjects.name, SysComments.Text as [sql]");
			sb.AppendLine("FROM	dbo.sysobjects INNER JOIN dbo.syscomments ON dbo.sysobjects.id = dbo.syscomments.id");
			sb.AppendLine("WHERE (dbo.sysobjects.category = 0) AND (dbo.sysobjects.xtype = 'V') AND NOT (dbo.sysobjects.name LIKE 'gen_%')");
			sb.AppendLine("ORDER BY dbo.sysobjects.name");
			return sb.ToString();
		}

		private string GetSqlForStoredProceduresParameters()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT	dbo.sysobjects.name, dbo.sysobjects.id,");
			sb.AppendLine("		dbo.syscolumns.name AS ColName,");
			sb.AppendLine("		dbo.systypes.name AS ColType,");
			sb.AppendLine("		dbo.syscolumns.length");
			sb.AppendLine("FROM	dbo.sysobjects INNER JOIN");
			sb.AppendLine("		dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN");
			sb.AppendLine("		dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype");
			sb.AppendLine("WHERE	(dbo.sysobjects.category = 0) AND");
			sb.AppendLine("		(dbo.sysobjects.xtype = 'P') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'gen_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");
			sb.AppendLine("		dbo.systypes.name <> 'sysname' AND");
			sb.AppendLine("		(dbo.sysobjects.uid in (select uid from dbo.sysusers))");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("		dbo.sysobjects.name, dbo.syscolumns.name");
			return sb.ToString();
		}

		private string GetSqlForStoredProceduresBody(string spName)
		{			
			StringBuilder sb = new StringBuilder();

			DataSet ds = DatabaseHelper.ExecuteDataset(_connectString, "sp_helptext [" + spName + "]");
			if (ds.Tables.Count > 0)
			{
				foreach (DataRow dr in ds.Tables[0].Rows)
				{
					sb.Append((string)dr[0]);
				}

				string[] arr = sb.ToString().Split('\n');
				sb = new StringBuilder();

				bool inBody = false;
				foreach (string lineText in arr)
				{
					if (inBody)
					{
						sb.AppendLine(lineText);
					}
					else if (!inBody && lineText.ToLower() == "as")
					{
						inBody = true;
					}
				}
			}

			return sb.ToString();
		}

		private string GetSqlForStoredProcedures()
		{
			StringBuilder sb = new StringBuilder();
			sb.AppendLine("SELECT	dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.sysobjects.name");
			sb.AppendLine("FROM	dbo.sysobjects");
			sb.AppendLine("WHERE (dbo.sysobjects.category = 0) AND");
			sb.AppendLine("		(dbo.sysobjects.xtype = 'P') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'gen_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");
			sb.AppendLine("		(dbo.sysobjects.uid in (select uid from dbo.sysusers))");
			sb.AppendLine("ORDER BY dbo.sysobjects.name");
			return sb.ToString();
		}

    private string GetSqlForForeignKeys(string parentTable, string childTable, string constraintName)
    {
      StringBuilder sb = new StringBuilder();
      sb.Append("DECLARE @parent_table varchar(256) ");
      sb.Append("DECLARE @child_table varchar(256) ");
      sb.Append("DECLARE @constraint varchar(256) ");
      sb.Append("SET @parent_table = '").Append(parentTable).Append("' ");
      sb.Append("SET @child_table = '").Append(childTable).Append("' ");
      sb.Append("SET @constraint = '").Append(constraintName).Append("' ");
      sb.Append("DECLARE @FKeys TABLE  (  parentTable varchar(100) NOT NULL,   childTable varchar(100) NOT NULL,   childColumn varchar(100) NOT NULL,   constid int NOT NULL,   keyno smallint NOT NULL  )   ");
      sb.Append("DECLARE @PKeys TABLE  (  parentTable varchar(100) NOT NULL,  childTable varchar(100) NOT NULL,  parentColumn varchar(100) NOT NULL,  constid int NOT NULL,   keyno smallint NOT NULL  )   ");
      sb.Append("INSERT INTO @FKeys   SELECT DISTINCT   	parent.name parentTable,   	child.name childTable,    ");
      sb.Append("	syscolumns.name as childColumn,    	sysforeignkeys.constid,   	sysforeignkeys.keyno   FROM    	sysforeignkeys     ");
      sb.Append("		inner join sysobjects child on fkeyid = child.id     ");
      sb.Append("		inner join sysobjects parent on rkeyid = parent.id     ");
      sb.Append("		inner join syscolumns on syscolumns.id = sysforeignkeys.fkeyid AND syscolumns.colorder = sysforeignkeys.fkey     ");
      sb.Append("INSERT INTO @PKeys    ");
      sb.Append("SELECT    	parent.name parentTable,   	child.name childTable,   	syscolumns.name as parentColumn,  	sysforeignkeys.constid,   	sysforeignkeys.keyno   ");
      sb.Append("FROM     ");
      sb.Append("	sysforeignkeys  ");
      sb.Append("	inner join sysobjects child on fkeyid = child.id     ");
      sb.Append("	inner join sysobjects parent on rkeyid = parent.id     ");
      sb.Append("	inner join syscolumns on syscolumns.id = sysforeignkeys.rkeyid AND syscolumns.colorder = sysforeignkeys.rkey   ");
      sb.Append("SELECT  ");
      sb.Append("	p.parentTable , ");
      sb.Append("	p.parentColumn,  ");
      sb.Append("	f.childTable,  ");
      sb.Append("	f.ChildColumn ,  ");
      sb.Append("	so.name as roleName ");
      sb.Append("	FROM @FKeys f  ");
      sb.Append("		INNER JOIN @PKeys p on f.constid=p.constID and f.keyno=p.keyno  ");
      sb.Append("		INNER JOIN sysobjects so on so.id = p.constid   ");
      sb.Append("WHERE  ");
      sb.Append("	f.parentTable = @parent_table  ");
      sb.Append("AND  ");
      sb.Append("	f.childTable = @child_table  ");
      sb.Append("AND  ");
      sb.Append("	so.name = @constraint  ");
      sb.Append("union ");
      sb.Append("SELECT  ");
      sb.Append("@parent_table as parentTable, ");
      sb.Append("lower (@parent_table) + '_id' as parentColumn, ");
      sb.Append("@child_table as child_table, ");
      sb.Append("c.COLUMN_NAME as child_column, ");
      sb.Append("@constraint as roleName ");
      sb.Append("FROM INFORMATION_SCHEMA.COLUMNS c WHERE ");
      sb.Append("   c.TABLE_NAME = @child_table  ");
      sb.Append("AND ");
      sb.Append("	c.COLUMN_NAME like 'fk_to_' + @parent_table + '%' ");
      sb.Append("AND ");
      sb.Append("@parent_table like '%_HIST' ");
      sb.Append("	AND  ");
      sb.Append("SUBSTRING(@constraint,	CHARINDEX ('__',@constraint)+ 2 ,LEN(@constraint)- CHARINDEX ('__',@constraint)) ");
      sb.Append(" =  ");
      sb.Append("SUBSTRING(c.COLUMN_NAME,	CHARINDEX ('__',c.COLUMN_NAME)+ 2 ,LEN(c.COLUMN_NAME)- CHARINDEX ('__',c.COLUMN_NAME)) ");
      return sb.ToString();
		}

		#endregion

		public enum ImportReturnConstants
		{
			Aborted,
			Success,
			NoChange,
		}

		public ImportReturnConstants ImportModel(WidgetsphereGeneratorProject currentGraph)
		{
			ProgressingForm progressingForm = null;
			try
			{
				WidgetsphereGeneratorProject newProject = new WidgetsphereGeneratorProject();
				GetProjectFromSqlSchema(newProject);
				
				Widgetsphere.Generator.Models.Database newDatabase = ((ModelRoot)newProject.RootController.Object).Database;
				Widgetsphere.Generator.Models.Database oldDatabase = ((ModelRoot)currentGraph.RootController.Object).Database;

				ImportVerificationForm F = new ImportVerificationForm();
				ImportModelSettings settings = F.Populate(oldDatabase, newDatabase);

				//Check if there are any changes and if so show the form
				if (F.GetChangeCount() == 0)
				{
					return ImportReturnConstants.NoChange;
				}
				else if (F.ShowDialog() != System.Windows.Forms.DialogResult.OK)
				{
					return ImportReturnConstants.Aborted;
				}

				progressingForm = new ProgressingForm();
				progressingForm.Show();
				try
				{
					this.UpdateTables(newDatabase, oldDatabase, settings);

					#region Relations
					List<Relation> oldList = new List<Relation>();
					foreach (Relation r in oldDatabase.Relations)
						oldList.Add(r);

					oldDatabase.Relations.Clear();
					this.LoadRelations(currentGraph);

					//Now reset the role names where necessary
					foreach (Relation r1 in oldDatabase.Relations)
					{
						foreach (Relation r2 in oldList)
						{
							if (r1.Equals(r2) && (r1.RoleName != r2.RoleName)) r1.RoleName = r2.RoleName;
						}
					}

					#endregion

					//this.UpdateViews(newDatabase, oldDatabase, settings);

					//this.UpdateStoredProcedures(newDatabase, oldDatabase, settings);

					//Refresh the tree
					//currentGraph.RootController.Node.RefreshDeep();
					((ModelRootController)currentGraph.RootController).ClearTree();
				}
				catch (Exception ex)
				{
					throw;
				}
				finally
				{
					if (progressingForm != null)
						progressingForm.Close();
					progressingForm = null;
				}
				return ImportReturnConstants.Success;

			}
			catch (SqlException ex)
			{
				MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Information);
				return ImportReturnConstants.Aborted;
			}
			catch (Exception ex)
			{
				throw;
			}
			finally
			{
				if (progressingForm != null)
					progressingForm.Close();
				progressingForm = null;
			}

		}

		private void UpdateTables(Widgetsphere.Generator.Models.Database newDatabase, Widgetsphere.Generator.Models.Database oldDatabase, ImportModelSettings settings)
		{
			#region Update existing tables
			foreach (Table t in oldDatabase.Tables)
			{
				Table newT = newDatabase.Tables[t.Name];
				if (newT != null)
				{
					#region Update matching columns
					List<Reference> delRefList = new List<Reference>();
					foreach (Reference r in t.Columns)
					{
						Column oldCol = (Column)r.Object;
						Reference newColRef = newT.Columns.FindByName(oldCol.Name);
						Column newCol = null;
						if (newColRef != null) newCol = (Column)newColRef.Object;

						bool doUpdate = ((newCol != null) && (oldCol.CorePropertiesHash != newCol.CorePropertiesHash));
						if (doUpdate && settings.OverridePrimaryKey)
							doUpdate = (oldCol.CorePropertiesHashNoPK != newCol.CorePropertiesHashNoPK);

						if (doUpdate)
						{
							oldCol.CancelUIEvents = true;
							int colId = oldCol.Id;
							string colKey = oldCol.Key;
							bool isPrimaryKey = oldCol.PrimaryKey;
							newCol.CodeFacade = oldCol.CodeFacade;
							//newCol.SetId(colId);
							//newCol.SetKey(colKey);
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							newCol.XmlAppend(doc.DocumentElement);
							oldCol.XmlLoad(doc.DocumentElement);
							oldCol.SetId(colId);
							oldCol.SetKey(colKey);
							//oldCol.PrimaryKey = isPrimaryKey;
							oldCol.CancelUIEvents = false;
						}
						else if (newCol == null)
						{
							delRefList.Add(r);
						}

					}

					//Remove deleted fields
					foreach (Reference r in delRefList)
					{
						t.Columns.Remove(r);
					}
					#endregion

					#region Add new columns
					foreach (Reference r in newT.Columns)
					{
						Column c = (Column)r.Object;
						if (t.GetColumns().FirstOrDefault(x => x.Name == c.Name) == null)
						{
							Column newCol = oldDatabase.Columns.Add(c.Name);
							int colId = newCol.Id;
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							c.XmlAppend(doc.DocumentElement);
							newCol.XmlLoad(doc.DocumentElement);
							newCol.SetId(colId);
							newCol.ParentTableRef = t.CreateRef();

							Reference newRef = new Reference(t.Root);
							newRef.Ref = newCol.Id;
							newRef.RefType = ReferenceType.Column;
							t.Columns.Add(newRef);
						}
					}
					#endregion

					#region Delete old columns
					List<Column> delColumnList = new List<Column>();
					foreach (Reference r in t.Columns)
					{
						if (newT.GetColumns().FirstOrDefault(x => x.Name == ((Column)r.Object).Name) == null)
						{
							delColumnList.Add((Column)r.Object);
						}
					}
					foreach (Column c in delColumnList)
					{
						oldDatabase.Columns.Remove(c);
					}
					#endregion

					#region Update table properties
					t.CancelUIEvents = true;
					t.Description = newT.Description;
					t.AssociativeTable = newT.AssociativeTable;
					t.Generated = newT.Generated;
					t.HasHistory = newT.HasHistory;
					t.AllowModifiedAudit = newT.AllowModifiedAudit;
					t.AllowCreateAudit = newT.AllowCreateAudit;
					t.AllowTimestamp = newT.AllowTimestamp;
					t.IsTypeTable = newT.IsTypeTable;
					t.CreateMetaData = newT.CreateMetaData;
					t.FullIndexSearch = newT.FullIndexSearch;
					t.IsMetaData = newT.IsMetaData;
					t.IsMetaDataDefinition = newT.IsMetaDataDefinition;
					t.IsMetaDataMaster = newT.IsMetaDataMaster;
					t.DBSchema = newT.DBSchema;
					t.CancelUIEvents = false;
					#endregion
				}

			}
			#endregion

			#region Add new tables
			foreach (Table t in newDatabase.Tables)
			{
				if (!oldDatabase.Tables.Contains(t.Name))
				{
					Table newT = oldDatabase.Tables.Add(t.Name);
					XmlDocument doc = new XmlDocument();
					int tableId = newT.Id;
					doc.LoadXml("<a></a>");
					t.XmlAppend(doc.DocumentElement);
					newT.XmlLoad(doc.DocumentElement);
					newT.SetId(tableId);

					newT.Columns.Clear();
					foreach (Reference r in t.Columns)
					{
						Column newCol = oldDatabase.Columns.Add(((Column)r.Object).Name);
						int colId = newCol.Id;
						XmlDocument doc3 = new XmlDocument();
						doc3.LoadXml("<a></a>");
						((Column)r.Object).XmlAppend(doc3.DocumentElement);
						newCol.XmlLoad(doc3.DocumentElement);
						newCol.SetId(colId);
						newCol.ParentTableRef = newT.CreateRef();

						Reference newRef = new Reference(newT.Root);
						newRef.Ref = newCol.Id;
						newRef.RefType = ReferenceType.Column;
						newT.Columns.Add(newRef);
					}
					newT.Relationships.Clear();

				}
			}
			#endregion

			#region Delete removed tables
			List<Table> delTableList = new List<Table>();
			foreach (Table t in oldDatabase.Tables)
			{
				if (!newDatabase.Tables.Contains(t.Name))
				{
					delTableList.Add(t);
				}
			}
			foreach (Table t in delTableList)
			{
				oldDatabase.Tables.Remove(t);
			}
			#endregion
		}

		#region UPDATE CODE (NOT USED)
		/*

		private void UpdateViews(Widgetsphere.Generator.Models.Database newDatabase, Widgetsphere.Generator.Models.Database oldDatabase, ImportModelSettings settings)
		{
			#region Update existing items
			foreach (CustomView t in oldDatabase.CustomViews)
			{
				CustomView newV = newDatabase.CustomViews[t.Name];
				if (newV != null)
				{
					#region Update matching columns
					List<Reference> delRefList = new List<Reference>();
					foreach (Reference r in t.Columns)
					{
						CustomViewColumn oldCol = (CustomViewColumn)r.Object;
						Reference newColRef = newV.Columns.FindByName(oldCol.Name);
						CustomViewColumn newCol = null;
						if (newColRef != null) newCol = (CustomViewColumn)newColRef.Object;

						bool doUpdate = ((newCol != null) && (oldCol.CorePropertiesHash != newCol.CorePropertiesHash));
						if (doUpdate && settings.OverridePrimaryKey)
							doUpdate = (oldCol.CorePropertiesHashNoPK != newCol.CorePropertiesHashNoPK);

						if (doUpdate)
						{
							oldCol.CancelUIEvents = true;
							int colId = oldCol.Id;
							string colKey = oldCol.Key;							
							newCol.CodeFacade = oldCol.CodeFacade;
							//newCol.SetId(colId);
							//newCol.SetKey(colKey);
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							newCol.XmlAppend(doc.DocumentElement);
							oldCol.XmlLoad(doc.DocumentElement);
							oldCol.SetId(colId);
							oldCol.SetKey(colKey);							
							oldCol.CancelUIEvents = false;
						}
						else if (newCol == null)
						{
							delRefList.Add(r);
						}

					}

					//Remove deleted fields
					foreach (Reference r in delRefList)
					{
						t.Columns.Remove(r);
					}
					#endregion

					#region Add new columns
					foreach (Reference r in newV.Columns)
					{
						CustomViewColumn c = (CustomViewColumn)r.Object;
						if (t.GetColumns().FirstOrDefault(x => x.Name == c.Name) == null)
						{
							CustomViewColumn newCol = oldDatabase.CustomViewColumns.Add(c.Name);
							int colId = newCol.Id;
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							c.XmlAppend(doc.DocumentElement);
							newCol.XmlLoad(doc.DocumentElement);
							newCol.SetId(colId);
							newCol.ParentViewRef = t.CreateRef();

							Reference newRef = new Reference(t.Root);
							newRef.Ref = newCol.Id;
							newRef.RefType = ReferenceType.CustomViewColumn;
							t.Columns.Add(newRef);
						}
					}
					#endregion

					#region Delete old columns
					List<CustomViewColumn> delColumnList = new List<CustomViewColumn>();
					foreach (Reference r in t.Columns)
					{
						if (newV.GetColumns().FirstOrDefault(x => x.Name == ((CustomViewColumn)r.Object).Name) == null)
						{
							delColumnList.Add((CustomViewColumn)r.Object);
						}
					}
					foreach (CustomViewColumn c in delColumnList)
					{
						oldDatabase.CustomViewColumns.Remove(c);
					}
					#endregion

					#region Update item properties
					t.CancelUIEvents = true;
					t.Description = newV.Description;
					t.Generated = newV.Generated;
					t.SQL = newV.SQL;
					t.CancelUIEvents = false;
					#endregion
				}

			}
			#endregion

			#region Add new items
			foreach (CustomView t in newDatabase.CustomViews)
			{
				if (!oldDatabase.CustomViews.Contains(t.Name))
				{
					CustomView newV = oldDatabase.CustomViews.Add(t.Name);
					XmlDocument doc = new XmlDocument();
					int tableId = newV.Id;
					doc.LoadXml("<a></a>");
					t.XmlAppend(doc.DocumentElement);
					newV.XmlLoad(doc.DocumentElement);
					newV.SetId(tableId);

					newV.Columns.Clear();
					foreach (Reference r in t.Columns)
					{
						CustomViewColumn newCol = oldDatabase.CustomViewColumns.Add(((CustomViewColumn)r.Object).Name);
						int colId = newCol.Id;
						XmlDocument doc3 = new XmlDocument();
						doc3.LoadXml("<a></a>");
						((Column)r.Object).XmlAppend(doc3.DocumentElement);
						newCol.XmlLoad(doc3.DocumentElement);
						newCol.SetId(colId);
						newCol.ParentViewRef = newV.CreateRef();

						Reference newRef = new Reference(newV.Root);
						newRef.Ref = newCol.Id;
						newRef.RefType = ReferenceType.Column;
						newV.Columns.Add(newRef);
					}					

				}
			}
			#endregion

			#region Delete removed items
			List<CustomView> delViewList = new List<CustomView>();
			foreach (CustomView t in oldDatabase.Tables)
			{
				if (!newDatabase.CustomViews.Contains(t.Name))
				{
					delViewList.Add(t);
				}
			}
			foreach (CustomView t in delViewList)
			{
				oldDatabase.CustomViews.Remove(t);
			}
			#endregion
		}

		private void UpdateStoredProcedures(Widgetsphere.Generator.Models.Database newDatabase, Widgetsphere.Generator.Models.Database oldDatabase, ImportModelSettings settings)
		{
			#region Update existing items
			foreach (CustomStoredProcedure t in oldDatabase.CustomStoredProcedures)
			{
				CustomStoredProcedure newSP = newDatabase.CustomStoredProcedures[t.Name];
				if (newSP != null)
				{
					#region Update matching columns
					List<Reference> delRefList = new List<Reference>();
					foreach (Reference r in t.Parameters)
					{
						Parameter oldParameter = (Parameter)r.Object;
						Reference newParameterRef = newSP.Parameters.FindByName(oldParameter.Name);
						Parameter newParameter = null;
						if (newParameterRef != null) newParameter = (Parameter)newParameterRef.Object;

						bool doUpdate = ((newParameter != null) && (oldParameter.CorePropertiesHash != newParameter.CorePropertiesHash));
						if (doUpdate && settings.OverridePrimaryKey)
							doUpdate = (oldParameter.CorePropertiesHashNoPK != newParameter.CorePropertiesHashNoPK);

						if (doUpdate)
						{
							oldParameter.CancelUIEvents = true;
							int colId = oldParameter.Id;
							string colKey = oldParameter.Key;
							newParameter.CodeFacade = oldParameter.CodeFacade;
							//newCol.SetId(colId);
							//newCol.SetKey(colKey);
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							newParameter.XmlAppend(doc.DocumentElement);
							oldParameter.XmlLoad(doc.DocumentElement);
							oldParameter.SetId(colId);
							oldParameter.SetKey(colKey);
							oldParameter.CancelUIEvents = false;
						}
						else if (newParameter == null)
						{
							delRefList.Add(r);
						}

					}

					//Remove deleted fields
					foreach (Reference r in delRefList)
					{
						t.Columns.Remove(r);
					}
					#endregion

					#region Add new columns
					foreach (Reference r in newSP.Columns)
					{
						CustomStoredProcedureColumn c = (CustomStoredProcedureColumn)r.Object;
						if (t.GetColumns().FirstOrDefault(x => x.Name == c.Name) == null)
						{
							CustomStoredProcedureColumn newCol = oldDatabase.CustomStoredProcedureColumns.Add(c.Name);
							int colId = newCol.Id;
							XmlDocument doc = new XmlDocument();
							doc.LoadXml("<a></a>");
							c.XmlAppend(doc.DocumentElement);
							newCol.XmlLoad(doc.DocumentElement);
							newCol.SetId(colId);
							newCol.ParentRef = t.CreateRef();

							Reference newRef = new Reference(t.Root);
							newRef.Ref = newCol.Id;
							newRef.RefType = ReferenceType.CustomStoredProcedureColumn;
							t.Columns.Add(newRef);
						}
					}
					#endregion

					#region Delete old columns
					List<CustomStoredProcedureColumn> delColumnList = new List<CustomStoredProcedureColumn>();
					foreach (Reference r in t.Columns)
					{
						if (newSP.GetColumns().FirstOrDefault(x => x.Name == ((CustomStoredProcedureColumn)r.Object).Name) == null)
						{
							delColumnList.Add((CustomStoredProcedureColumn)r.Object);
						}
					}
					foreach (CustomStoredProcedureColumn c in delColumnList)
					{
						oldDatabase.CustomStoredProcedureColumns.Remove(c);
					}
					#endregion

					#region Update item properties
					t.CancelUIEvents = true;
					t.Description = newSP.Description;
					t.Generated = newSP.Generated;
					t.CancelUIEvents = false;
					t.SQL = newSP.SQL;
					#endregion
				}

			}
			#endregion

			#region Add new items
			foreach (CustomStoredProcedure t in newDatabase.CustomStoredProcedures)
			{
				if (!oldDatabase.CustomStoredProcedures.Contains(t.Name))
				{
					CustomStoredProcedure newSP = oldDatabase.CustomStoredProcedures.Add(t.Name);
					XmlDocument doc = new XmlDocument();
					int tableId = newSP.Id;
					doc.LoadXml("<a></a>");
					t.XmlAppend(doc.DocumentElement);
					newSP.XmlLoad(doc.DocumentElement);
					newSP.SetId(tableId);

					newSP.Parameters.Clear();
					foreach (Reference r in t.Parameters)
					{
						Parameter parameter = oldDatabase.CustomRetrieveRuleParameters.Add();						
						int colId = parameter.Id;
						XmlDocument doc3 = new XmlDocument();
						doc3.LoadXml("<a></a>");
						((CustomStoredProcedureColumn)r.Object).XmlAppend(doc3.DocumentElement);
						parameter.XmlLoad(doc3.DocumentElement);
						parameter.SetId(colId);
						parameter.ParentTableRef = newSP.CreateRef();
					}					

				}
			}
			#endregion

			#region Delete removed items
			List<CustomStoredProcedure> delCustomStoredProcedureList = new List<CustomStoredProcedure>();
			foreach (CustomStoredProcedure t in oldDatabase.CustomStoredProcedures)
			{
				if (!newDatabase.CustomStoredProcedures.Contains(t.Name))
				{
					delCustomStoredProcedureList.Add(t);
				}
			}
			foreach (CustomStoredProcedure t in delCustomStoredProcedureList)
			{
				oldDatabase.CustomStoredProcedures.Remove(t);
			}
			#endregion
		}

		 */
		#endregion

	}	

	internal class ImportModelSettings
	{
		public bool OverridePrimaryKey { get; set; }
	}

	internal class ProgressEventArgs : System.EventArgs
	{
		public int PercentDone { get; set; }
		public string Text { get; set; }
	}

}